<!--#include file="./include/PageVBHeader.inc"-->
<%@ Register TagPrefix="GUS" TagName="SysProfile" src="./include\SysProfile.ascx" %>
<%@ Register TagPrefix="GUS" TagName="AdminHeader" src="AdminHeader.ascx" %>
<%@ Register TagPrefix="GUS" TagName="AdminFooter" src="AdminFooter.ascx" %>
<%@ Register TagPrefix="GUS" TagName="BibleBookList" src="BibleBookList.ascx" %>

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="GUSNET" %>

<script language="VB" runat="server">


   Sub Page_Load( sender as Object,  e as EventArgs ) 
   	 if not (Page.IsPostBack) then
	 	TitleID.Text = Request.QueryString("TitleID")
	 	ChapterID.Text = Request.QueryString("ChapterID")
	 	''Keyword.Text = Request.QueryString("Keyword")
		''ReturnURL.NavigateUrl = Request.UrlReferrer.ToString()

		if ChapterID.Text = ""	 then 
			ChapterID.Text = "1"
		end if 
		
		if Keyword.Text <>"" then
			ChapterList.Text = "Search: " + Keyword.Text
			BookChapStr.Text = ""
			VerseList.Text   = GetFoundedVerseListStr(Keyword.Text)
		elseif TitleID.Text <>""  then
			ChapterList.Text = GetChapterListStr(TitleID.Text, ChapterID.Text)
			BookChapStr.Text = GetThisBookChapterStr()
			VerseList.Text   = GetVerseListStr(TitleID.Text, ChapterID.Text)
		end if
		
		
	 end if 
	 ''ShowImgs()
	 ''testbible()
   End Sub
   
   
   sub testbible()
   	 Dim sql as String
	 Dim  ConnStr as String = SysProfile.ConnectionString
	 Dim obj as GUSDBUtil =   new GUSDBUtil (ConnStr)
	 Dim  dbr as SqlDataReader
	 sql = "SELECT         Title_ID, Title_CN, Title_CNAB, Title_EN, Title_ENAB, Title_New FROM BibleTitle"
	 dbr = obj.GetSQLDataReader(sql)
	  while (dbr.Read())
	  	Response.Write(dbr.GetValue(1).ToString() + "<br>")
	  End While 
	  dbr.Close()
   end sub

   Function GetThisBookChapterStr()
   	 Dim sql, st, ThisTitle as String
	 Dim  ConnStr as String = SysProfile.ConnectionString
	 Dim obj as GUSDBUtil =   new GUSDBUtil (ConnStr)
	 Dim  dbr as SqlDataReader
	 
	 If TitleID.Text = "" then
	 	Exit Function
	 end if
	 sql = "SELECT  Title_ID, Title_CN, Title_CNAB, Title_EN, Title_ENAB, Title_New FROM BibleTitle where Title_ID='"+ TitleID.Text +"'"
	 'Response.Write(sql)
	 'exit function
	 dbr = obj.GetSQLDataReader(sql)
	  while (dbr.Read())
	  	ThisTitle = dbr.GetValue(1).ToString() 
	  End While 
	  dbr.Close()
	  
	  st = "<b>" + ThisTitle + "</b>"
	  if ChapterID.Text <>"" and st <>"" then
	  	st = st + "<i> [Chap: <b>"+ ChapterID.Text +"</b> ]</i> <br>"
	  end if 
	  
	  GetThisBookChapterStr = st 
   end Function
   
   Function GetChapterListStr(tID, cID)
   	 Dim sql as String
	 Dim  ConnStr as String = SysProfile.ConnectionString
	 Dim obj as GUSDBUtil =   new GUSDBUtil (ConnStr)
	 Dim  dbr as SqlDataReader
	 Dim st as string
	 DIM maxChap , i
	 maxChap = 0
	 sql = "SELECT MAX(BibleContent.Content_Chap) AS Expr1 FROM BibleTitle INNER JOIN BibleContent ON BibleTitle.Title_ID = BibleContent.Content_Title WHERE         (BibleTitle.Title_ID = "+ tID +")"
	 dbr = obj.GetSQLDataReader(sql)
	  while (dbr.Read())
	  	maxChap = dbr.GetValue(0)
	  End While 
	  dbr.Close()
	  
	  for i = 1 to maxChap
	  	if cID.ToString = i.ToString() then
		  	st = st + "<b>["+ i.ToString() +"]</b> "
		else
		  	st = st + "<a href='ReadBible.aspx?TitleID="+ tID.ToString() +"&ChapterID="+ i.ToString() +"'>["+ i.ToString() +"]</a> "
		end if 
	  next
	  GetChapterListStr = st
   End Function 
   
   Function GetVerseListStr(tID, cID)
   	 Dim sql as String
	 Dim  ConnStr as String = SysProfile.ConnectionString
	 Dim obj as GUSDBUtil =   new GUSDBUtil (ConnStr)
	 Dim  dbr as SqlDataReader
	 Dim st as string
	 DIM maxChap , i
	 maxChap = 0
	 sql = "SELECT  BibleContent.Content_Chap, BibleContent.Content_Sec, BibleContent.Content_CN FROM  BibleTitle INNER JOIN BibleContent ON " & _
	 "  BibleTitle.Title_ID = BibleContent.Content_Title WHERE (BibleTitle.Title_ID = '"& tID &"') and (BibleContent.Content_Chap='"& cID &"')"& _
	 " ORDER BY  BibleContent.Content_Chap, BibleContent.Content_Sec "
	 dbr = obj.GetSQLDataReader(sql)
	 st = "<table>"
	  while (dbr.Read())
	  	st = st & "<tr valign=top>"
		st = st & "<td width=5% align=center>"&  dbr.GetValue(1)  &"</td>"
		st = st & "<td width=95% align=left>"&  dbr.GetValue(2)  &"</td>"
		st = st + "</tr>"
	  End While 
	  st = st & "</table>"
	  dbr.Close()
	  
	  GetVerseListStr = st
   End Function 
   
   
   Function GetFoundedVerseListStr(keywd)
   	 Dim sql as String
	 Dim  ConnStr as String = SysProfile.ConnectionString
	 Dim obj as GUSDBUtil =   new GUSDBUtil (ConnStr)
	 Dim  dbr as SqlDataReader
	 Dim st as string
	 DIM maxChap , i
	 DIM encodeKeywd
	 Dim encode   as System.Text.Encoding 
	
	encode = System.Text.Encoding.GetEncoding("utf-8")

	''encodeKeywd = encode.GetString(keywd, 0, len(keywd))
	
	encodeKeywd = keywd

	 maxChap = 0
	 sql = ""

	 sql = "SELECT BibleTitle.Title_ID, Title_CN, Title_CNAB, BibleContent.Content_Chap, BibleContent.Content_Sec, BibleContent.Content_CN FROM  BibleTitle INNER JOIN BibleContent ON " & _
	 "  BibleTitle.Title_ID = BibleContent.Content_Title WHERE (BibleContent.Content_CN like '%"& encodeKeywd &"%')"& _
	 " ORDER BY  BibleTitle.Title_ID, BibleContent.Content_Chap, BibleContent.Content_Sec "
	 ''Response.Write(sql)
	 'Response.end()
	 dbr = obj.GetSQLDataReader(sql)
	 st = "<table>"
	  while (dbr.Read())
	  	st = st & "<tr valign=top>"
		st = st & "<td width=15% align=left nowrap><b>"&  dbr.GetValue(2)  & dbr.GetValue(3) & ":" & dbr.GetValue(4) & "</b></td>"
		'st = st & "<td width=85% align=left>"&  dbr.GetValue(5).ToString().Replace(keywd.ToString(), " <font color=red>" + keywd.ToString() + "</font> ")  &"</td>"
		st = st & "<td width=85% align=left>"&  dbr.GetValue(5).ToString()  &"</td>"
		st = st + "</tr>"
		maxChap = maxChap +1
	  End While 
	  st = st & "</table>"
	  if maxChap >0 then
	  	st += "<b>Total found: "& maxChap &"</b>"
	  else
	  	st += "Not Found !"
	  end if
	  dbr.Close()
	  
	  GetFoundedVerseListStr = st
   End Function 
   
   Sub SearchKeyword(sender As Object, e As EventArgs)
	  if Keyword.Text = "" then
	  	exit sub
	  end if  

			ChapterList.Text = "Search: " + Keyword.Text
			BookChapStr.Text = ""
			VerseList.Text   = GetFoundedVerseListStr(Keyword.Text)

   End Sub
   
   
</script>

<Html>
<head>
<title>Bible Tools</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
</head>
<Body BgColor=White>
<GUS:SysProfile id="SysProfile" runat="server"/>
<GUS:AdminHeader id="AdminHeader" runat="server"/>
<Form  runat="server">

<table valign=top width=770 border=1>
	<tr valign=top>
		<td valign=top width=150 nowrap >
			<table border=1>
				<tr>
					<td>
					Keyword Search:<br>
					<asp:TextBox Id="Keyword" RunAt="server" />
					<br>
					<Asp:button id="Search" OnClick="SearchKeyword" Text="Search" runat="server"/>
					</td>
				</tr>
			</table>	

		<GUS:BibleBookList id="BibleBookList" runat="server"/>			
		</td>
		<td valign=top align=left width=650>
			<!--
			<LI>BookID:  <asp:Label Id="TitleID" RunAt="server" Text=""  Visible="false"/>
			<LI>Chapter: <asp:Label Id="ChapterID" RunAt="server" Text=""  Visible="false" /> <br>
			-->
			
			<asp:Label Id="BookChapStr" RunAt="server" Text=""  Visible="true"/>
			<asp:Label Id="ChapterList" RunAt="server" Text=""  Visible="true"/>
		
			<hr>
			
			<asp:Label Id="VerseList" RunAt="server" Text=""  Visible="true"/>
			
		</td>
	</tr>
</table>






<asp:Label Id="DebugMsg" RunAt="server" Visible="False" />


<br><br>
<GUS:AdminFooter id="AdminFooter" runat="server"/>
</form>

</Body>
</Html>

